﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DBBase;
using System.IO;

using System.Reflection; // 引用这个才能使用Missing字段
using System.Xml;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections;

namespace Business
{
    public partial class DriverStatistics : Form
    {

        ArrayList ccllx = new ArrayList();
        public int ijilu;

        public DriverStatistics()
        {
            InitializeComponent();
          
            ijilu = 0;
            getcllxnew();
        }

        public void getcllxnew()
        {
            Query mquery = new Query();
            string sql;
            sql = string.Format(@"Select distinct NAME from USERE  where JSLX='引车员'");
            DataTable dt = mquery.GetTable(sql);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i]["NAME"].ToString().Trim().Length > 1)
                    {
                        ccllx.Add(dt.Rows[i]["NAME"].ToString().Trim());
                        ijilu++;
                    }
                }
            }
        }
        public void getneirong()
        {
            string sql = "";
            string sql2 = "";
            string sql3 = "";
            string sql4 = "";
            Query mquery = new Query();
            for (int i = 0; i < ijilu; i++)
            {
                sql = string.Format(@"Select AT_CLLX from Autos,Examines,Detects Where Examines.EXAMINE_ID=Detects.EXAMINE_ID and Examines.AT_ID=Autos.AT_ID and AT_CLLX='{0}'", ccllx[i]);

                int izong = mquery.selectDBcount(sql);

                sql2 = string.Format(@"Select AT_CLLX from Autos,Examines,Detects Where Examines.EXAMINE_ID=Detects.EXAMINE_ID and Examines.AT_ID=Autos.AT_ID and AT_CLLX='{0}' and DET_OK=1", ccllx[i]);

                int izong2 = mquery.selectDBcount(sql2);

                sql3 = string.Format(@"Select AT_CLLX from Autos,Examines,Detects Where Examines.EXAMINE_ID=Detects.EXAMINE_ID and Examines.AT_ID=Autos.AT_ID and AT_CLLX='{0}' and DET_OK=1 and DET_ITERAT<1", ccllx[i]);

                int izong3 = mquery.selectDBcount(sql3);

                sql4 = string.Format(@"Select AT_CLLX from Autos,Examines,Detects Where Examines.EXAMINE_ID=Detects.EXAMINE_ID and Examines.AT_ID=Autos.AT_ID and AT_CLLX='{0}' and DET_OK=1 and DET_ITERAT>0", ccllx[i]);

                int izong4 = mquery.selectDBcount(sql4);
            }
        }
      
       
        void inicbx(ComboBox cbx, string dataname)
        {
            Query objquery = new Query();
            string ssqlex = "";
            ssqlex = string.Format(@"select distinct DET_YCY from Detects_SJA ");
            DataTable dt = objquery.GetTable(ssqlex);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                cbx.Items.Add(dt.Rows[i]["DET_YCY"].ToString().Trim());
                if (i == 0)
                    cbx.Text = dt.Rows[0]["DET_YCY"].ToString().Trim();
            }
        }
        private void button1_Click(object sender, EventArgs e)
        {
            string path = "";
            path = Application.StartupPath + path + DateTime.Now.ToString("yyyyMMdd") + ".xls";
            excel(path);
        }
        private void excel(string path)
        {
            try
            {
                StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("gb2312"));
                StringBuilder sb = new StringBuilder();
                // sb.Append("地区名" + "\t");
                sb.Append("统计时间从" + date_kaishi.Value.ToShortDateString() + "至" + date_jieshu.Value.ToShortDateString() + "\t");
                sb.Append(Environment.NewLine);
                for (int i = 0; i < lv.Columns.Count; i++)
                    sb.Append(lv.Columns[i].Text.ToString() + "\t");
                sb.Append(Environment.NewLine);

                foreach (ListViewItem li in lv.Items)
                {
                    for (int i = 0; i < lv.Columns.Count; i++)
                        sb.Append(li.SubItems[i].Text + "\t");
                    sb.Append(Environment.NewLine);
                }
                sw.Write(sb.ToString());
                sw.Flush();
                sw.Close();
                MessageBox.Show("成功保存于" + path + "文件！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void renyuantongji_Load(object sender, EventArgs e)
        {
            inicbx(comboBox1, "YCY");
        }

        private void skinButton1_Click(object sender, EventArgs e)
        {
            DataTable dtable = new DataTable("Rock");
            //set columns names
            dtable.Columns.Add("1", typeof(System.String));
            dtable.Columns.Add("2", typeof(System.String));
            dtable.Columns.Add("3", typeof(System.String));
            dtable.Columns.Add("4", typeof(System.String));
            dtable.Columns.Add("5", typeof(System.String));
            dtable.Columns.Add("6", typeof(System.String));
            dtable.Columns.Add("7", typeof(System.String));
            dtable.Columns.Add("8", typeof(System.String));
            dtable.Columns.Add("9", typeof(System.String));
            dtable.Columns.Add("10", typeof(System.String));
            dtable.Columns.Add("11", typeof(System.String));
            dtable.Columns.Add("12", typeof(System.String));
            dtable.Columns.Add("13", typeof(System.String));

            Query mquery = new Query();
            string sql = "";
            string sql2 = "";
            string sql3 = "";
            string sql4 = "";
            string sql5 = "";
            string sql6 = "";
            string sql7 = "";
            int izs1 = 0;
            int izs2 = 0;
            int izs3 = 0;
            int izs4 = 0;
            int izs5 = 0;
            int izs6 = 0;
            int izs7 = 0;

            DataRow drow = dtable.NewRow();
            for (int i = 0; i < ijilu; i++)
            {
                sql = string.Format(@"Select AT_CLLX from [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and DET_YCY='{0}' and 
Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}'", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong = mquery.selectDBcount(sql);
                izs1 = izs1 + izong;

                sql2 = string.Format(@"Select AT_CLLX from [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and DET_YCY='{0}' and 
                Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}'and DET_JYZJL=1 and DET_JYCS=1", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong2 = mquery.selectDBcount(sql2);
                izs2 = izs2 + izong2;

                sql3 = string.Format(@"Select AT_CLLX from [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and DET_YCY='{0}' and 
                Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}' and DET_JYCS=1", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong3 = mquery.selectDBcount(sql3);
                izs3 = izs3 + izong3;

                sql4 = string.Format(@"Select AT_CLLX from [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and DET_YCY='{0}' and 
                Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}'and DET_JYZJL=1 and DET_JYCS=2 ", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong4 = mquery.selectDBcount(sql4);
                izs4 = izs4 + izong4;

                sql5 = string.Format(@"Select AT_CLLX from [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and DET_YCY='{0}' and 
                Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}' and DET_JYCS=1", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong5 = mquery.selectDBcount(sql5);
                izs5 = izs5 + izong5;


                sql6 = string.Format(@"Select AT_CLLX from [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and DET_YCY='{0}'and Detects_SJA.DET_JYCS=1 and 
                Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}' and DET_JYXM like '%C1%'", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong6 = mquery.selectDBcount(sql6);
                izs6 = izs6 + izong6;

                sql7 = string.Format(@"Select AT_CLLX from [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and DET_YCY='{0}'and Detects_SJA.DET_JYCS=1 and 
                Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}' and DET_JYXM like '%C1%' and AT_SYXZ like '%非营运%' and (AT_CLLX like '%轿车%' or AT_CLLX like '%客车%'or AT_CLLX like '%面包车%') and AT_HDZK<10 ", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong7 = mquery.selectDBcount(sql7);
                izs7 = izs7 + izong7;

                drow = dtable.NewRow();
                drow["1"] = string.Format("{0}", i + 1);
                drow["2"] = ccllx[i];
                drow["3"] = string.Format("{0}", izong);
                drow["4"] = string.Format("{0}", izong2);
                drow["5"] = string.Format("{0}", izong - izong2);
                drow["6"] = string.Format("{0}%", 100 * izong2 / (izong > 0 ? izong : 1));
                drow["7"] = string.Format("{0}", izong4);
                drow["8"] = string.Format("{0}%", 100 * izong4 / (izong3 > 0 ? izong3 : 1));
                drow["9"] = string.Format("{0}", izong5);//初检
                drow["10"] = string.Format("{0}", izong - izong5);
                drow["11"] = string.Format("{0}", izong7);//40
                drow["12"] = string.Format("{0}", izong6 - izong7);//100
                drow["13"] = string.Format("{0}", izong - (izong6 - izong7) - izong7 - (izong - izong5));//不检
                dtable.Rows.Add(drow);
            }

            drow = dtable.NewRow();
            drow["1"] = string.Format("{0}", ijilu + 1);
            drow["2"] = "合计";
            drow["3"] = string.Format("{0}", izs1);
            drow["4"] = string.Format("{0}", izs2);
            drow["5"] = string.Format("{0}", izs1 - izs2);
            drow["6"] = string.Format("{0}%", 100 * izs2 / (izs1 > 0 ? izs1 : 1));
            drow["7"] = string.Format("{0}", izs4);
            drow["8"] = string.Format("{0}%", 100 * izs4 / (izs3 > 0 ? izs3 : 1));
            drow["9"] = string.Format("{0}", izs5);
            drow["10"] = string.Format("{0}", izs1 - izs5);
            drow["11"] = string.Format("{0}", izs7);
            drow["12"] = string.Format("{0}", izs6 - izs7);
            drow["13"] = string.Format("{0}", izs1 - (izs6 - izs7) - izs7 - (izs1 - izs5));
            dtable.Rows.Add(drow);

            multiColHeaderDgv2.DataSource = dtable;
        }
    }
}
